1. Field of the Invention
The present invention relates generally to replication of information in data processing environments and, more particularly, to system and methods for replication of sequences which occur in database systems (e.g., Oracle sequences).
2. Description of the Background Art
Computers are very powerful tools for storing and providing access to vast amounts of information. Computer databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical database is an organized collection of related information stored as “records” having “fields” of information. As an example, a database of employees may have a record for each employee where each record contains fields designating specifics about the employee, such as name, home address, salary, and the like.
Between the actual physical database itself (i.e., the data actually stored on a storage device) and the users of the system, a database management system or DBMS is typically provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about underlying hardware-level details. Typically, all requests from users for access to the data are processed by the DBMS. For example, information may be added or removed from data files, information may be retrieved from or updated in such files, and so forth, all without user knowledge of the underlying system implementation. In this manner, the DBMS provides users with a conceptual view of the database that is removed from the hardware level.
DBMS systems have long since moved from a centralized mainframe environment to a de-centralized or distributed environment. Today, one generally finds database systems implemented as one or more PC “client” systems, for instance, connected via a network to one or more server-based database systems (SQL database server). Commercial examples of these “client/server” systems include Powersoft® clients connected to one or more Sybase® Adaptive Servers® Enterprise database servers. Both Powersoft® and Sybase® Adaptive Servers® Enterprise (formerly Sybase® SQL Server®) are available from Sybase, Inc. of Dublin, Calif. The general construction and operation of database management systems, including “client/server” relational database systems, is well known in the art. See e.g., Date, C., “An Introduction to Database Systems, Volume I and II”, Addison Wesley, 1990; the disclosure of which is hereby incorporated by reference.
Each day more and more users base their business operations on mission-critical systems which store information on server-based database systems, such as Sybase® Adaptive Servers® Enterprise. As a result, the operations of the business are dependent upon the availability of data stored in their databases. Because of the mission-critical nature of these systems, users of these systems need to protect themselves against loss of the data due to software or hardware problems, disasters such as floods, earthquakes, or electrical power loss, or temporary unavailability of systems resulting from the need to perform system maintenance.
One well-known approach for users to guard against loss of critical business data is to maintain a standby or replicate database. A replicate database is a duplicate or mirror copy of a given database that is maintained either locally at the same site as the primary database or remotely at a different location than the primary database. The availability of a replicate copy of a given database enables a user (e.g., corporation or other business) to reconstruct a copy of a given database in the event of the loss, destruction, or unavailability of the primary database.
Today, a lot of database processing involves managing data that follows some sort of sequence, such as a sequence of customer numbers, account numbers, part numbers, or the like. A “sequence” number or identity may be used for this purpose. Of particular interest are Oracle sequences or “Sequence Objects.” Each Oracle sequence is a database object that provides a sequential series of numbers. Here, the database system maintains a sequence counter, so a unique sequence number may be provided, as required for the task at hand. In Oracle database systems, sequence numbers are Oracle integers defined in the database of up to 38 digits. A sequence definition indicates general information, including: the name of the sequence; whether the sequence ascends or descends; the interval between numbers; and whether the (Oracle) database system should cache sets of generated sequence numbers in memory. Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. Sequence number generation is useful to generate unique primary keys and to coordinate keys across multiple rows or tables. A sequence is especially useful in a multi-user environment for generating unique sequential numbers without the overhead of disk I/O or transaction locking.
Sequence numbers are used by SQL statements that reference the sequence. For example, a database user can issue a statement to generate a new sequence number or use the current sequence number. After a statement in a user's session generates a sequence number, the particular sequence number is available only to that session. Each user that references a sequence has access to his or her own current sequence number. Oracle sequences have a “cache” option that pre-allocates a set of sequence numbers and keeps them in memory, so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, the Oracle database system reads another set of numbers into the cache. The Oracle database system might skip sequence numbers if one chooses to cache a set of sequence numbers. In that case, when an instance abnormally shuts down, sequence numbers that have been cached but not used are lost. Sequence numbers that have been used but not saved are lost as well.
When a sequence number is generated, the sequence is incremented, independent of any associated transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
In the area of database replication, sequences pose a special set of problems. In particular, due to the ability to lose cached sequence values or to lose sequence values from rollback processing, each possible sequence value may not be used or replicated. Nevertheless, it is desirable to provide support for the replication of sequence objects, in order to provide complete support for Oracle databases. This requirement is particularly important for standby implementations where the standby site is expected to provide an identical image of activity from the primary site. Given this customer expectation, there is great interest in providing a replication solution for replicating sequences. The present invention fulfills this and other needs.